package br.fatec.ads.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.fatec.ads.connection.DataSource;
import br.fatec.ads.exception.DataBaseException;
import br.fatec.ads.exception.RespostaException;
import br.fatec.ads.model.Pergunta;
import br.fatec.ads.model.PerguntaPK;
import br.fatec.ads.model.Resposta;

public class RespostaDAO {
	private static RespostaDAO respostaDAO;
	private static String INCLUIR_RESPOSTA = "EXEC P_RESPOSTA_INCLUIR ?, ?, ?, ?";
	private static String ALTERAR_RESPOSTA = "EXEC P_RESPOSTA_ALTERAR ?, ?, ?";
	private static String EXCLUIR_RESPOSTA = "EXEC P_RESPOSTA_EXCLUIR ?, ?";
	private static String CONSULTAR_RESPOSTA = "EXEC P_RESPOSTA_CONSULTAR ?";
	private static String LISTAR_RESPOSTA = "EXEC P_RESPOSTA_LISTAR ?";
	
	public RespostaDAO() {
		super();
	}
	
	public static RespostaDAO getInstance(){
		if(respostaDAO == null){
			respostaDAO = new RespostaDAO();
		}
		
		return respostaDAO;
	}
	
    public void incluirResposta(Resposta resposta) throws RespostaException, DataBaseException{
        Connection connection = null;
        PreparedStatement query = null;

        try{
            try{
                connection = DataSource.getConnection();
                query = connection.prepareStatement(INCLUIR_RESPOSTA);

                int i = 0;
                query.setInt(++i, resposta.getPergunta().getIdPergunta());
                query.setString(++i, resposta.getDescResposta());
                query.setString(++i, resposta.getNroResposta());
                query.setString(++i, resposta.getUsuario());
                
                query.executeUpdate();
            }catch(SQLException cause){
                throw new RespostaException("Problemas ao INSERIR a Resposta!", cause);
            }finally{
                DataSource.close(connection);
                DataSource.close(query);
            }
        } catch(DataBaseException cause){
            throw new DataBaseException(cause);
        }
    }
    
    public void alterarResposta(Resposta resposta) throws RespostaException, DataBaseException {
        Connection connection = null;
        PreparedStatement query = null;

        try{
            try{
                connection = DataSource.getConnection();
                query = connection.prepareStatement(ALTERAR_RESPOSTA);

                int i = 0;
                query.setInt(++i, resposta.getIdResposta());
                query.setString(++i, resposta.getNroResposta());
                query.setString(++i, resposta.getUsuario());

                query.executeUpdate();
            }catch(SQLException cause){
                throw new RespostaException("Problemas ao ALTERAR a Resposta!", cause);
            }finally{
                DataSource.close(connection);
                DataSource.close(query);
            }
        }catch(DataBaseException cause){
            throw new DataBaseException(cause);
        }
    }
    
    public void excluirResposta(Resposta resposta) throws RespostaException, DataBaseException{
        Connection connection = null;
        PreparedStatement query = null;

        try{
            try{
                connection = DataSource.getConnection();
                query = connection.prepareStatement(EXCLUIR_RESPOSTA);

                int i = 0;
                query.setInt(++i, resposta.getIdResposta());
                query.setString(++i, resposta.getUsuario());

                query.executeUpdate();
            }catch(SQLException cause){
                throw new RespostaException("Problemas ao EXCLUIR a Resposta!", cause);
            }finally{
                DataSource.close(connection);
                DataSource.close(query);
            }
        }catch(DataBaseException cause){
            throw new DataBaseException(cause);
        }
    }

    public Resposta getResposta(int idResposta) throws RespostaException, DataBaseException{
    	Connection connection = null;
    	PreparedStatement query = null;
    	ResultSet resultSet = null;
    	Resposta resposta = null;
    	
    	try{
    		try{
    			resposta = new Resposta();
    			
    			connection = DataSource.getConnection();
    			query = connection.prepareStatement(CONSULTAR_RESPOSTA);
    			
    			int i = 0;
    			query.setInt(++i, idResposta);
    			
    			resultSet = query.executeQuery();
    			
    			while(resultSet.next()){
    				Pergunta pergunta = new Pergunta();
    				pergunta.setIdPergunta(resultSet.getInt("IDPERGUNTA"));
    				
    				resposta.setPergunta(pergunta);
    				resposta.setIdResposta(resultSet.getInt("IDRESPOSTA"));
    				resposta.setDescResposta(resultSet.getString("DESCRESPOSTA"));
    				resposta.setNroResposta(resultSet.getString("NRORESPOSTA"));
    				resposta.setDataHora(resultSet.getTimestamp("DTHRINCLUSAO"));
    				resposta.setUsuario(resultSet.getString("USUARIOINCLUSAO"));
    			}
    		}catch (SQLException cause) {
    			throw new RespostaException("Problemas ao CONSULTAR a Resposta!", cause);
			}finally{
				DataSource.close(connection);
				DataSource.close(query);
				DataSource.close(resultSet);
			}
    	}catch (DataBaseException cause) {
    		throw new DataBaseException(cause);
		}
    	
    	return resposta;
    }
    
    public List listarResposta(Resposta resposta) throws RespostaException, DataBaseException{
    	Connection connection = null;
    	PreparedStatement query = null;
    	ResultSet resultSet = null;
    	List listaResposta = new ArrayList();
    	
    	try{
    		try{
    			connection = DataSource.getConnection();
    			query = connection.prepareStatement(LISTAR_RESPOSTA);
    			
    			int i = 0;
    			query.setInt(++i, resposta.getPergunta().getIdPergunta());
    			
    			resultSet = query.executeQuery();
    			
    			while(resultSet.next()){
    				Resposta resp = new Resposta();
    				
    				Pergunta pergunta = new Pergunta();
    				pergunta.setIdPergunta(resultSet.getInt("IDPERGUNTA"));
    				
    				resp.setPergunta(pergunta);
    				resp.setIdResposta(resultSet.getInt("IDRESPOSTA"));
    				resp.setDescResposta(resultSet.getString("DESCRESPOSTA"));
    				resp.setNroResposta(resultSet.getString("NRORESPOSTA"));
    				resp.setAtivo(resultSet.getString("ATIVO"));
    				resp.setDataHora(resultSet.getTimestamp("DTHRINCLUSAO"));
    				resp.setUsuario(resultSet.getString("USUARIOINCLUSAO"));
    				
    				listaResposta.add(resp);
    			}
    		}catch (SQLException cause) {
    			throw new RespostaException("Problemas ao LISTAR a Resposta!", cause);
			}finally{
				DataSource.close(connection);
				DataSource.close(query);
				DataSource.close(resultSet);
			}
    	}catch (DataBaseException cause) {
    		throw new DataBaseException(cause);
		}
    	
    	return listaResposta;
    }
}